package com.hz.ys_financial.dbsql;



import com.hz.framework.MyInterFace.SystemInterFace;

/**
 * *****************************************************************************
 *               Copyright(c) 2010 by 哈尔滨华泽数码科技有限公司.
 *                       All rights reserved.
 *******************************************************************************
 *     File Name         :  com.hz.ys_stock.dbsql.StockSql.java
 *     Description(说明)	: 采购单付款信息 sql 语句
 * -----------------------------------------------------------------------------
 *     No.        Date              Revised by           Description	
 *     	1	  Jun 10, 2011           韩飞              Created
 *****************************************************************************
 */
public class FinancialSql implements SystemInterFace {
	
//	查询收付款信息表
	public static final String SEL_FINANCIAL = "select * from takingsorpay where 1=1 ";
	
//	查询 未付款 采购单信息    //pur_supp：采购单供应商视图
	public static final String SEL_PURCHASE = "select p.*,r.suppliername,b.summoney from pres_purchase p "
			+" left join pres_supplier r on  p.supplierid = r.supplierid "
			+" left join (select sum(summoney)as summoney,purchaseid from pres_buymak group by purchaseid) b on b.purchaseid = p.purchaseid "
			+" where tpflag != 'y' order by purchaseid";
	
//	更新采购单信息
	public static final String UPD_PURCHASE_INFO = "update pres_purchase set tpflag = 'y' where purchaseid in ";
	
//	删除供应商信息 假删除
	public static final String DEL_PAYMONEY = "update takingsorpay set flag = 'n' where tpid = ?";
	
	
//	查询收付款信息表
	public static final String SEL_PAYMONEY_INFO = "select * from takingsorpay where tpid = ? ";
	
//	查询 未付款 采购单信息    //pur_supp：采购单供应商视图
	public static final String SEL_PAYMONEY_PURCHASE = "select p.*,r.suppliername,b.summoney from pres_purchase p "
			+" left join pres_supplier r on  p.supplierid = r.supplierid "
			+" left join (select sum(summoney)as summoney,purchaseid from pres_buymak group by purchaseid) b on b.purchaseid = p.purchaseid ";  
	
	/**
	 *  订单预收款信息
	 */
//	查询 订单预付款信息	
	public static final String SEL_PLANGATHER = "select o.*,c.clientName,tpperson,tpdate,tpmoney,realmoney,tpway,tpflag,billtype,bill,billmoney,finalflag" 
			+" from orderinfo o "
			+" left join clientinfo c on c.clientId = o.clientId"
			+" left join takingsorpay t on o.orderId = t.orderId and tpflag = 'plan'"
			+" where frontcash !='0' ";
//	根据orderId查询 订单预付款信息
	public static final String SEL_PLANGATHER_INFO = "select t.*,o.name from takingsorpay t left join orderinfo o  on o.orderId = t.orderId "
			+" where tpflag  = 'plan' and t.orderId = ? ";
	
	
//	查询 未结算的所有订单信息
	public static final String SEL_ORDER_LIST = "select o.*,c.clientname,c.unitname,t.finalflag from orderinfo o "
			+" left join clientinfo c on  o.clientid = c.clientid "
			+" left join takingsorpay t on t.orderid like concat('%',o.orderid,'%') and t.tpflag = 'order' "
			+" where (t.finalflag != '已结算' and t.finalflag != '余付款') or t.finalflag is null order by orderId";
	
//	查询 订单结算信息
	public static final String SEL_ENDGATHER_ORDER = "select o.*,c.clientname,c.unitname,t.finalflag from orderinfo o "
			+" left join clientinfo c on  o.clientid = c.clientid "
			+" left join takingsorpay t on t.orderid like concat('%',o.orderid,'%') and t.tpflag = 'order' ";
	
	/**
	 *  其他收入支出信息
	 */
	public static final String SEL_OTHERPAY_LIST = "select * from takingsorpay "
			+" where (tpflag  = 'tak' or tpflag = 'pay') and flag = 'y'";
	
	/**
	 * 客户财务明细
	 */
	public static final String SEL_CLIENTFINANCE = "select c.clientid,c.clientname,c.phone,c.unitname," +
			" ifnull(ordernum,0) as ordernum," + //订单数量
			" ifnull(ordermoney,0) as ordermoney," + //订单总额
			" ifnull(realmoney,0) as realmoney," + //已付金额
			" ifnull(billmoney,0) as billmoney," + //发票金额
			" ifnull((ordermoney - (ifnull(realmoney,0))) ,0) as debtCash " + //未付金额
			" from clientinfo c ";
	//收支明细
	public static final String SEL_INCOMEORPAY = "select * from takingsorpay where realmoney !=0 ";
}
